<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*,java.io.*, KC.cimaV2.*, java.util.*, java.net.*, jxl.*, java.text.*, com.oreilly.servlet.MultipartRequest, com.oreilly.servlet.multipart.*, java.util.*"%>
<%@ include file="Connections/conDB.jsp" %>
<%
response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); //HTTP 1.1
response.setHeader("Pragma","no-cache"); //HTTP 1.0
response.setDateHeader ("Expires", -1); //prevents caching at the proxy server
%>
<% int validaSession = 1; %>
<%@ include file="Connections/session.jsp" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
	<link rel="stylesheet" href="estilo.css" type="text/css">
	<title>CIMA v2 - Datos de archivo</title>
	
<script>
function subirFacturas(){
	document.forma.subiendo.value = "OK";
	document.forma.submit();
}
</script>
</head>

<%
SimpleDateFormat sdfRequest = new SimpleDateFormat( "dd/MM/yyyy" );

String subiendo = request.getParameter("subiendo");
if(subiendo != null && subiendo.equals("OK")){
	Driver Driver = (Driver)Class.forName(MM_conDB_DRIVER).newInstance();
	Connection conn = DriverManager.getConnection(MM_conDB_STRING,MM_conDB_USERNAME,MM_conDB_PASSWORD);

	ArrayList facturas = (ArrayList)session.getAttribute("arrayFacturas");

	String mensaje = "";
	try{
		mensaje = Factura.subirFacturas(conn.createStatement(), facturas, sesLogin);
	}catch(SQLException e){
		mensaje = e.getMessage();
	}
	%>
	<script>
		window.opener.frm_filtrosConciliacion.submit();
		alert('<%=mensaje%>');
		window.close();
	</script>
<%}else{
	NumberFormat     nf2               = NumberFormat.getInstance();
	nf2.setMaximumFractionDigits(2);
	nf2.setMinimumFractionDigits(2);
	
	String archivo = "";
	ArrayList row = new ArrayList();
	String ruta = application.getRealPath("/")+"Conciliaciones\\";

	try {
		MultipartRequest multi =  new MultipartRequest(request, ruta , 10 * 1024 * 1024);
			archivo = multi.getFilesystemName("archivo");
			//out.println("<br> 1 "+ruta+""+archivo);
			//filetype = multi.getContentType(paramname);
			//file = multi.getFile(paramname);
	
			File file = new File(ruta+""+archivo);
			
		if (file.exists()) {
			Workbook workbook = Workbook.getWorkbook(file);
			int nohojas = workbook.getNumberOfSheets();
			//out.println("<br> -- nh " + nohojas);
			Sheet sheet = workbook.getSheet(0);
			int no_columnas = sheet.getColumns();
			//out.println("<br> -- nc " + no_columnas);
			int no_filas = sheet.getRows();
			//out.println("<br> -- nf " + no_filas);
			try {
				if(sheet.findLabelCell("INICIAL")!=null && sheet.findLabelCell("NO.CARRO")!=null 
					&& sheet.findLabelCell("GUIA")!=null && sheet.findLabelCell("FECHA")!=null 
					&& sheet.findLabelCell("CVE OP OR")!=null  &&  sheet.findLabelCell("CVE OP DE")!=null 
					&&  sheet.findLabelCell("FACTURA")!=null  &&  sheet.findLabelCell("FECHA FACT")!=null 
					&&  sheet.findLabelCell("IMPORTE")!=null  &&  sheet.findLabelCell("IVA")!=null 
					&&  sheet.findLabelCell("TOTAL")!=null && sheet.findLabelCell("MONEDA")!=null 
					&& sheet.findLabelCell("OBS")!=null){
	
					for (int i = 1; i<=(no_filas-1); i++) {
						if( (sheet.getCell(3,i)).getContents() == null || (sheet.getCell(3,i)).getContents().equals("") )break;
						String [] row_tmp = {(String) (sheet.getCell(3,i)).getContents(), (String) (sheet.getCell(4,i)).getContents(),
										(String) (sheet.getCell(5,i)).getContents(),  (String) (sheet.getCell(6,i)).getContents(),
										(String) (sheet.getCell(10,i)).getContents(), (String) (sheet.getCell(12,i)).getContents(),
										(String) (sheet.getCell(16,i)).getContents(), (String) (sheet.getCell(17,i)).getContents(),
										(String) (sheet.getCell(18,i)).getContents(), (String) (sheet.getCell(19,i)).getContents(),
										(String) (sheet.getCell(20,i)).getContents(), (String) (sheet.getCell(21,i)).getContents(),
										(String) (sheet.getCell(22,i)).getContents(), "0", "0", "0" };
						row.add(row_tmp);
		
					} // fin del for
		
			} else {//fin del If si las Etiquetas coinciden
			%>
				<table width="50%" border="0" cellpadding="0" cellspacing="0">
					<tr> 				
						<td class="titulosBE" align="Center"><b>Las Columnas en el Archivo de Excel No Coinciden</b></td>
					</tr>
				</table>
				<%
			} // fin del If LABELS existen
	
			} // Fin del Try para Validar si las Etiquetas Existen
			catch (Exception e) {
					%><%=e%>
					<table width="50%" border="0" cellpadding="0" cellspacing="0">
					  <tr> 
							<td class="titulosBE" align="Center"><b>Las Columnas en el Archivo de Excel No Coinciden</b></td>
					  </tr>
	</table>
					<%
			}
		}else{  // Si Existe el Archivo
			out.println("Archivo no existe");
		}
	
	} catch (Exception e){
		out.print("<script>");
		out.print("alert('No se pudo subir el archivo.'"+e+"');");
		out.print("window.close();");
		out.print("</script>");
	}
	
	%>
	<body background="images/fondo.jpg">
	<br>
	<center>
	  <table>
	  <%
	  String factura;
	  String fecha;
	  String str_fechaDetalle;
	  String [] strs_fechaDetalle;
	  String importe;
	  String iva;
	  String total;
	  String factura_anterior = "";
	  String fecha_anterior = "";
	%>  
	  <table width="530" border="0" cellspacing="0" cellpadding="0">
		<tr align="center"> 
		  <td colspan="8" class="titulo">Nuevas Facturas</td>
		</tr>
		<tr class="tituloColumna">
			<td>&nbsp;</td>
			<td>No. Factura</td>
			<td>Fecha Factura</td>
			<td>Importe</td>
			<td>IVA</td>
			<td>Total</td>
			<td>Estatus</td>
		</tr>	
	<%
	  int cuantos = 1;
	  
	  double importeT = 0;
	  double ivaT = 0;
	  double totalT = 0;
	  
	  double importeTotal = 0;
	  double ivaTotal = 0;
	  double totalTotal = 0;
	  int i=0;
	  int int_primero = 0;
	  for(;i<row.size();i++){
			Object [] registro = (Object[])row.get(i);
	
			factura = (String)registro[6];
			SimpleDateFormat sdfFinal = new SimpleDateFormat( "yyyy/MM/dd" );
			
			str_fechaDetalle = (String)registro[3];
			SimpleDateFormat sdfDetalle = new SimpleDateFormat( "MM/dd/yyyy" );
			strs_fechaDetalle = str_fechaDetalle.split( "/" );
			str_fechaDetalle = 	(strs_fechaDetalle[0].length() == 1 ? "0" : "" ) + strs_fechaDetalle[0] + "/" +
								(strs_fechaDetalle[1].length() == 1 ? "0" : "" ) + strs_fechaDetalle[1] + "/" +
								"20"+strs_fechaDetalle[2];
			str_fechaDetalle = sdfFinal.format( sdfDetalle.parse( str_fechaDetalle ) );
			registro[3] = str_fechaDetalle;
			
			fecha = (String)registro[7];
			SimpleDateFormat sdf = new SimpleDateFormat( "ddMMyyyy" );
			fecha = (fecha.length() == 7 ? "0" : "")+fecha;
			fecha = sdfFinal.format( sdf.parse( fecha ) );
			registro[7] = fecha;
			
			importe = (String)registro[8];
			iva = (String)registro[9];
			total = (String)registro[10];
			importe = importe.replaceAll(",","");
			registro[8] = importe;
			iva = iva.replaceAll(",","");
			registro[9] = iva;
			total = total.replaceAll(",","");
			registro[10] = total;
			
			if(!factura_anterior.equals(factura)&&i>0){
	  		%>
				<tr>
					<td class="dato"><%=cuantos++%></td>
					<td class="dato"><%=factura_anterior%></td>
					<td class="dato"><%=fecha_anterior%></td>
					<td class="datoImporte">$<%=nf2.format(new Double(importeTotal).doubleValue())%></td>
					<td class="datoImporte">$<%=nf2.format(new Double(ivaTotal).doubleValue())%></td>
					<td class="datoImporte">$<%=nf2.format(new Double(totalTotal).doubleValue())%></td>
					<td class="dato">No Conciliada</td>
				</tr>
			<%
			importeT += importeTotal;
			ivaT += ivaTotal;
			totalT += totalTotal;
			
			( (Object[])row.get(int_primero) )[13] = "0"+importeTotal;
			( (Object[])row.get(int_primero) )[14] = "0"+ivaTotal;
			( (Object[])row.get(int_primero) )[15] = "0"+totalTotal;
			
			int_primero = i;
						
			importeTotal = 0;
			ivaTotal = 0;
			totalTotal = 0;
			}
			
			importeTotal += Double.parseDouble( importe );
			ivaTotal += Double.parseDouble( iva );
			totalTotal += Double.parseDouble( total );
			
			fecha_anterior = fecha;
			factura_anterior = factura;
		}
		if(i>0){
			%>
			<tr>
				<td class="dato"><%=cuantos++%></td>
				<td class="dato"><%=factura_anterior%></td>
				<td class="dato"><%=fecha_anterior%></td>
				<td class="datoImporte">$<%=nf2.format(new Double(importeTotal).doubleValue())%></td>
				<td class="datoImporte">$<%=nf2.format(new Double(ivaTotal).doubleValue())%></td>
				<td class="datoImporte">$<%=nf2.format(new Double(totalTotal).doubleValue())%></td>
				<td class="dato">No Conciliada</td>
			</tr>
			<%
			importeT += importeTotal;
			ivaT += ivaTotal;
			totalT += totalTotal;
			
			( (Object[])row.get(int_primero) )[13] = "0"+importeTotal;
			( (Object[])row.get(int_primero) )[14] = "0"+ivaTotal;
			( (Object[])row.get(int_primero) )[15] = "0"+totalTotal;
			
		}
			session.setAttribute("arrayFacturas",row);
			%>
			<tr>
				<td colspan="3" align="right" class="totales">Totales:</td>
				<td align="right" class="totales">$<%=nf2.format(importeT)%></td>
				<td align="right" class="totales">$<%=nf2.format(ivaT)%></td>
				<td align="right" class="totales">$<%=nf2.format(totalT)%></td>
				<td class="totales">&nbsp;</td>
			</tr>
	  </table>
	 </table>
 </center>
	<br />
	<table border="0" cellpadding="0" cellspacing="0" width="260">
		<tr>
						<td background="images/botonFondo.gif" class="boton"><a href="javascript: subirFacturas();"><img src="images/v32/botonInicio.gif" border="0" align="left" />&nbsp;Subir Facturas</a></td>
	
						<td background="images/botonFondo.gif" class="boton"><a href="javascript: window.close();"><img src="images/v32/botonInicio.gif" border="0" align="left" />&nbsp;Cancelar Facturas</a></td>
		</tr>
	</table>
	<form name="forma" method="post" action="conciliacion_showFile.jsp">
	<input name="subiendo" type="hidden">
	</form>
	</body>
	<script>
	<%
	int int_restopx = cuantos*15;
	out.println( "if( 170+"+int_restopx+" <= 400 ){" );
	out.println( "     window.moveTo( ( (screen.width - 650) / 2 ),( (screen.height - ( 170+"+int_restopx+")) / 2 ) );" );
	out.println( "     window.resizeTo(650 ,170+"+int_restopx+");" );
	out.println( "}else{");
	out.println( "     window.moveTo( ( (screen.width - 650) / 2 ),( (screen.height - ( 400 )) / 2 ) );" );
	out.println( "     window.resizeTo(650 ,400);" );
	out.println( "}");
	%>
	</script>
<%}%>
</html>